﻿using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using System.Data;
using BEGDC.Utility.NHibernate;
using Castle.Services.Transaction;
using System.Collections;
using System.Xml;
using Castle.Facilities.NHibernateIntegration;

namespace BEGDC.PreContract.BLL
{
	public partial class Province: IProvince
	{
        public BEGDC.PreContract.DAL.Province GetProvinceByName(string provinceCode)
        {
            BEGDC.PreContract.DAL.Province province = BEGDC.PreContract.DAL.Province.FindFirst(new NHibernate.Expression.EqExpression(BEGDC.PreContract.DAL.Province.CONST_ProvinceCode, provinceCode));

            if (province == null)
                province = BEGDC.PreContract.DAL.Province.FindFirst(new NHibernate.Expression.LikeExpression(BEGDC.PreContract.DAL.Province.CONST_ProvinceCode,string.Format("%{0}%", provinceCode)));
            return province;

        }
        //Joan.lu 2008-09-19
        //Get Active Province dimention by user permission
        public DataTable GetProvByPermission(long employeeID)
        { 
        
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT a.* FROM dbo.Province a INNER JOIN dbo.ProvinceAssignment b ON a.ProvinceID=b.ProvinceID WHERE a.Active=1 AND b.EmployeeID=" + employeeID.ToString() + " ORDER BY a.SortNumber";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }        
        }

        //Edwin.fan 2008-9-23
        //Get the province with the max ProvinceId
        public DataTable GetMaxProvince()
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT max(ProvinceID) FROM Province";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
     
        }

        //yuliang 2010-5-15
        //Get by filter
        [Transaction(TransactionMode.Requires)]
        public DataTable GetProvinceAndCluster_DataTable(string filter)
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = @"select p.*,pc.ClusterCode,pc.ClusterEName,pc.ClusterCName,pc.Sort as ClusterSort 
                                    from dbo.Province p
                                    inner join dbo.ProvinceCluster pc on p.ClusterID = pc.ProvinceClusterID and pc.IsActive = 1";
                if (filter.Trim() != string.Empty)
                    strSql += " WHERE " + filter;

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }
	}
}



